
package DAO;

import DTO.*;
import Exception.KwickKioskDBException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;

/**
 * This class represents abstraction for data access from promotion table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
public class PromoDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(
            PromoDAO.class.getName());
    /**
     * constructor for this class.
     * @param conn connection object
     */
    public PromoDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method adds promotion in database.
     * @param promoName  a string for name of promotion
     * @param startDate  a string representing promotion start date
     * @param endDate    a string representing promotion end date
     * @param promoAmount a double promotion amount for this promotion
     * @param storeCardReq integer indicating whether promotion has pre requirement of store card or not.
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addPromo(String promoName,
            String startDate,
            String endDate,
            double promoAmount,
            int storeCardReq) throws KwickKioskDBException {

        String sqlText = "INSERT INTO promotion (promotionName, "
                + "startDate, endDate, promoAmount, storeCardReq) VALUES "
                + "('" + promoName + "', "
                + "'" + startDate + "', "
                + "'" + endDate + "', "
                + "'" + promoAmount + "', "
                + storeCardReq + ")";
        return updateDB(sqlText);
    }

    public boolean updatePromo(Promotion p) throws KwickKioskDBException {

        String sqlText = "UPDATE promotion SET "
                + "promotionName='" + p.getName() + "', "
                + "startDate='" + p.getStartDate() + "', "
                + "endDate='" + p.getEndDate() + "', "
                + "promoAmount='" + p.getAmount() + "', "
                + "storeCardReq=" + p.isStoreCardReq()
                + " WHERE prid=" + p.getID();
        return updateDB(sqlText);
    }

   /**
     * This method removes a promotion from database.
     * @param prid an integer for promotion id in database
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean removePromotion(int prid) throws KwickKioskDBException {
        String sqlText = "DELETE FROM promotion WHERE prid="
                + prid;
        boolean result = updateDB(sqlText);
        return result;
    }

    /**
     * This method associates a new item with a promotion.
     * @param upc  a string for item code
     * @param prid an integer for promotion code
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addItemToPromotion(String upc, int prid)
            throws KwickKioskDBException {

        String sqlText = "INSERT INTO hasdiscount "
                + "(upc, prid) "
                + "VALUES ("
                + "'" + upc + "', " + prid + ")";
        return updateDB(sqlText);
    }

   /**
     * This method removes an item from promotion.
     * @param upc a string for item code
     * @param prid an integer for promotion code
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean removeItemFromPromotion(String upc, int prid)
            throws KwickKioskDBException {

        String sqlText = "DELETE FROM hasdiscount WHERE "
                + "upc='" + upc + "' and prid='" + prid + "'";

        return updateDB(sqlText);
    }

     /**
     * This method checks if any promotion is associated with an item.
     * @param upc a string for item code
     * @param prid an integer for promotion code
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean isItemInPromotion(String upc, int prid)
            throws KwickKioskDBException {

        boolean result = false;
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from hasdiscount d "
                    + "where d.upc='" + upc + "' and d.prid=" + prid;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            result = rs.next();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : getPromotionList" + ex);
            throw new KwickKioskDBException("PromoDAO : getPromotionList",
                    ex.getMessage());
        }
        return result;
    }

    /**
     * This method retrieves all promotions associated with an item.
     * @param upc a string representing item code in database
     * @return a list of promotions
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Promotion> getPromotionList(String upc)
            throws KwickKioskDBException {
        // End date string format: yyyy-mm-dd
        ArrayList<Promotion> list = new ArrayList<Promotion>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select p.prid, p.promotionName, p.startDate, p.endDate, "
                    + "p.promoAmount, p.storecardReq "
                    + "from promotion p, hasdiscount d "
                    + "where d.upc='" + upc + "' and d.prid=p.prid "
                    + "and curdate() >= p.startDate and curdate() <= p.endDate";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Promotion(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getDouble(5),
                        rs.getBoolean(6)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : getPromotionList" + ex);
            throw new KwickKioskDBException("PromoDAO : getPromotionList",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves all promotions from database
     * @return a list of promotions
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Promotion> getAllPromotions()
            throws KwickKioskDBException {
        ArrayList<Promotion> list = new ArrayList<Promotion>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from promotion p";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Promotion(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getDouble(5),
                        rs.getBoolean(6)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : getAllPromotions" + ex);
            throw new KwickKioskDBException("PromoDAO : getAllPromotions",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves clerk information from database
     * @param id an integer
     * @return ClerkDTO object encapsulating clerk information
     * @throws {@link KwickKioskDBException}
     */
    public Promotion searchByID(int id) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from promotion p "
                    + "where p.prid=" + id;

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                Promotion promo = new Promotion(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getDouble(5),
                        rs.getBoolean(6));
                sqlStatement.close();
                return promo;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : searchByID" + ex);
            throw new KwickKioskDBException("PromoDAO : searchByID",
                    ex.getMessage());
        }
        return null;
    }

   /**
     * This method searches an item in database by item name as criteria.
     * @param keywords  string representing search criteria
     * @return array list object of item data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Promotion> searchByName(String keywords)
            throws KwickKioskDBException {
        ArrayList<Promotion> list = new ArrayList<Promotion>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from promotion p ";
            StringTokenizer st = new StringTokenizer(keywords.trim());

            if (st.hasMoreTokens()) {
                sqlText += "where ";
            }

            while (st.hasMoreTokens()) {
                sqlText += "p.promotionName like '%" + st.nextToken() + "%'";
                if (st.hasMoreTokens()) {
                    sqlText += " and ";
                }
            }

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Promotion(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getDouble(5),
                        rs.getBoolean(6)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : searchByName" + ex);
            throw new KwickKioskDBException("PromoDAO : searchByName",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches a promotion by start date as input.
     * @param startDate a string for promotion start date
     * @return a list of promotions.
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Promotion> searchByStartDate(String startDate)
            throws KwickKioskDBException {
        // Start date string format: yyyy-mm-dd
        ArrayList<Promotion> list = new ArrayList<Promotion>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from promotion p "
                    + "where p.startDate='" + startDate + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Promotion(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getDouble(5),
                        rs.getBoolean(6)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : searchWithKeyWord" + ex);
            throw new KwickKioskDBException("PromoDAO : searchWithKeyWord",
                    ex.getMessage());
        }
        return list;
    }

   /**
     * This method returns all items associated with a promotion using.
     * @param prid an integer representing promotion id
     * @return list of items
     * @throws {@link KwickKioskDBException}
     */
        public ArrayList<Item> getItemByPromo(int prid)
            throws KwickKioskDBException {

        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                + "from item it "
                + "where it.upc in ( "
                + "select i.upc from item i, "
                + "hasdiscount h, promotion p "
                + "where i.upc=h.upc and h.prid=p.prid and p.prid="
                + prid + ")";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : getItemByPromo" + ex);
            throw new KwickKioskDBException("PromoDAO : getItemByPromo",
                        ex.getMessage());
        }
        return list;
    }

     /**
     * This method searches a promotion by end date as input.
     * @param endDate a string for promotion end date
     * @return a list of promotions.
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Promotion> searchByEndDate(String endDate)
            throws KwickKioskDBException {
        // End date string format: yyyy-mm-dd
        ArrayList<Promotion> list = new ArrayList<Promotion>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from promotion p "
                    + "where p.endDate='" + endDate + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Promotion(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getDouble(5),
                        rs.getBoolean(6)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "PromoDAO : searchByEndDate" + ex);
            throw new KwickKioskDBException("PromoDAO : searchByEndDate",
                    ex.getMessage());
        }
        return list;
    }

     /**
     * this method updates the database
     * @param sqlText a string for data manipulation  query
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            sqlStatement.close();
            if (value == 1) {
                conn.commit();
                return true;
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "PromoDAO : updateDB" + ex);
                throw new KwickKioskDBException("PromoDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "PromoDAO : updateDB" + ex2);
                throw new KwickKioskDBException("PromoDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }
}
